/*
 * 作者：姚俊
 * 功能：专利的逻辑处理
 * 日期：2015-05-29
 */
package admin.service.fruits;

import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Map;

import beanDao.MapToBean;

import teacher.bean.Patent;
import teacher.bean.Teacher;
import tools.StringDate;
import database.dao.MysqlCrud;

public class PatentService {
	
	
	/**
	 * @category 根据专利的ID来查询一个专利
	 * @author yaojun
	 * @param Patent Patent 传入一个专利信息的对象，该对象必须有专利信息的ID
	 * @return Patent 返回的是一个专利信息的对象
	 */
	public Patent getPatent(Patent patent)
	{
		Patent pat=new Patent();
		String sql="select * from Patent where patId=?";
		String[]  parameter={String.valueOf(patent.getPatId())};
        MysqlCrud mysqlcurd=new MysqlCrud();
		List<Map> lists=mysqlcurd.SelectData(sql, parameter);
		if(lists.size()>0&&lists!=null)
		{	
			Map map=lists.get(0);
			pat=new MapToBean().MapToPatent(map);
		}
		
		return pat;
	}	
	
	/**
	 * @category 根据专利信息的ID来修改这个专利的信息
	 * @author yaojun
	 * @param inn1 Patent 传入一个要用于修改的Patent对象
	 * @param inn2 Patent 传入一个根据这个Patent对象的ID来查找需要修改的哪一条专利信息数据
	 * @return boolean 成功则返回true
	 */
	public boolean updatePatent(Patent patent1,Patent patent2)
	{
		boolean b=false;
		String sql="update Patent set name=?,fiestInventor=?," +
		"empowerDate=?,empowerUnit=?,transferFunds=?,examine=?,applyNumber=?," +
		"number=?,upload=?,teaId=?,expClass=?,empowerNum=?,applyDate=?" +
		" where patId=?";
		
		String[] parameter={
				String.valueOf(patent1.getName()),
				String.valueOf(patent1.getFiestInventor()),
				String.valueOf(StringDate.DateToString(patent1.getEmpowerDate())),
				String.valueOf(patent1.getEmpowerUnit()),
				String.valueOf(patent1.getTransferFunds()),
				String.valueOf(patent1.getExamine()),
				String.valueOf(patent1.getApplyNumber()),
				String.valueOf(patent1.getNumber()),
				String.valueOf(patent1.getUpload()),
				String.valueOf(patent1.getTeaId()),
				String.valueOf(patent1.getExpClass()),
				String.valueOf(patent1.getEmpowerNum()),
				String.valueOf(StringDate.DateToString(patent1.getApplyDate())),
				String.valueOf(patent2.getPatId())
		};
		
		MysqlCrud mysqlcurd=new MysqlCrud();
		b=mysqlcurd.InsertAndUpdateData(sql, parameter);
		return b;
	}
	
	
	/**
	 * @category 查询所有专利并且进行分页
	 * @param pageSize int 一个页面要显示pageSize条个数据
	 * @param pageNow int 从pageNow当前的数据来显示
	 * @return patents List<Patent> 返回查询到的专利列表
	 */
	public List<Patent> getSegregatePatentByAll(int pageNow,int pageSize) {
		String sql= "select * from (select * from patent order by examine,expClass,empowerDate desc) as P WHERE P.examine>3 and P.patId LIMIT " +
				""+(pageNow-1)+" ,"+pageSize+"";
		int i=0;
		List<Patent> patents=new ArrayList<Patent>();
		MysqlCrud mysqlcurd=new MysqlCrud();
		List<Map> list = mysqlcurd.SelectData(sql, null);
		//System.out.println(list.size());
		while(i<list.size()){
			Map map=list.get(i);
			patents.add(new MapToBean().MapToPatent(map));
			i++;
		}
		return patents;
	}
	
	
	/**
	 * @category 查询所有专利
	 * @param pageSize int 一个页面要显示pageSize条个数据
	 * @param pageNow int 从pageNow当前的数据来显示
	 * @param name String 专利名称
	 * @return patents List<Patent> 返回查询到的专利列表
	 */
	public List<Patent> getSegregatePatentByName(String name,int pageNow,int pageSize) {
		String sql= "select * from (select * from patent order by examine,expClass,empowerDate desc) as P WHERE P.examine>3 and P.name like '%"+name+"%' and P.patId LIMIT " +
				""+(pageNow-1)+" ,"+pageSize+"";
		int i=0;
		List<Patent> patents=new ArrayList<Patent>();
		MysqlCrud mysqlcurd=new MysqlCrud();
		List<Map> list = mysqlcurd.SelectData(sql, null);
		//System.out.println(list.size());
		while(i<list.size()){
			Map map=list.get(i);
			patents.add(new MapToBean().MapToPatent(map));
			i++;
		}
		return patents;
	}
	
	
	
	/**
	 * @category 查询所有专利
	 * @param pageSize int 一个页面要显示pageSize条个数据
	 * @param pageNow int 从pageNow当前的数据来显示
	 * @param date1 Date 开始日期
	 * @param date2 Date 结束日期
	 * @return patents List<Patent> 返回查询到的专利列表
	 */
	public List<Patent> getSegregatePatentByDate(Date date1,Date date2,int pageNow,int pageSize) {
		String sql= "select * from (select * from patent order by examine,expClass,empowerDate desc) as P WHERE P.examine>3 and P.empowerDate>='"+date1+"' and P.empowerDate<='"+date2+"' and P.patId LIMIT " +
				""+(pageNow-1)+" ,"+pageSize+"";
		int i=0;
		List<Patent> patents=new ArrayList<Patent>();
		MysqlCrud mysqlcurd=new MysqlCrud();
		List<Map> list = mysqlcurd.SelectData(sql, null);
		//System.out.println(list.size());
		while(i<list.size()){
			Map map=list.get(i);
			patents.add(new MapToBean().MapToPatent(map));
			i++;
		}
		return patents;
	}
	
	
	/**
	 * @category 根据名称统计专利的数量
	 * @return sum int 专利的数量
	 */
		public int getCountPatentByName(String name)
		{
			int count=0;
			String sql=null;
			if(name.equals("")||name==null)
			{
				sql="select count(*) from patent where examine>3";
			}
			else
			{
				sql="select count(*) from patent where examine>3 and name like '%"+name+"%'";
			}
			MysqlCrud mysqlcurd=new MysqlCrud();
			List<Map> lists=mysqlcurd.SelectData(sql, null);
			Map map=lists.get(0);
			count=Integer.parseInt(map.get(0).toString());
			return count;
		}
		
		
		/**
		 * @category 根据日期统计专利的数量
		 * @return count int 专利的数量
		 */
		public int getCountPatentByDate(Date date1,Date date2)
		{
			int count=0;
			String sql="select count(*) from patent where examine>3 and empowerDate>='"+date1+"' and empowerDate<='"+date2+"'";
			MysqlCrud mysqlcurd=new MysqlCrud();
			List<Map> lists=mysqlcurd.SelectData(sql, null);
			Map map=lists.get(0);
			count=Integer.parseInt(map.get(0).toString());
			return count;
		}
		
		
		/**
		 * @author yaojun
		 * @category 按照审核的状态分页得到所有的专利
		 * @param state 0表示未审核，1表示已经审核，2表示审核不通过
		 * @return List
		 */
		public List<Patent> getSegregatePatentByState(int state,int pageNow,int pageSize) {
			String sql= "select * from patent WHERE examine>3 and examine=? and patId LIMIT " +
				""+(pageNow-1)+" ,"+pageSize+"";
			int i=0;
			List<Patent> patents=new ArrayList<Patent>();
			MysqlCrud mysqlcurd=new MysqlCrud();
			String[]  parameter={String.valueOf(state)};
			List<Map> list = mysqlcurd.SelectData(sql, parameter);
			//System.out.println(list.size());
			while(i<list.size()){
				Map map=list.get(i);
				patents.add(new MapToBean().MapToPatent(map));
				i++;
			}
			return patents;
		}

		
		
		/**
		 * @category 根据审核装填统计专利的数量
		 * @return count int 专利的数量
		 */
		public int getCountPatentByState(int state)
		{
			int count=0;
			String sql="select count(*) from patent where examine=?";
			MysqlCrud mysqlcurd=new MysqlCrud();
			String[]  parameter={String.valueOf(state)};
			List<Map> lists=mysqlcurd.SelectData(sql, parameter);
			Map map=lists.get(0);
			count=Integer.parseInt(map.get(0).toString());
			return count;
		}
		
		
		/**
		 * @author yaojun
		 * @category 根据不同条件查询所有专利并进行分页,针对特定的方向
		 * @return patents List<Patent> 返回查询到的专利列表
		 */
		public List<Patent> getSegregatePatentByDirection(Patent patent,String empowerDate2,String applyDate2,int direction,int pageNow,int pageSize,int state,List<String> orders) {
//			String sql= "select * from patent WHERE empowerDate>='"+date1+"' and empowerDate<='"+date2+"' and patId LIMIT " +
//					""+(pageNow-1)+" ,"+pageSize+"";
			String sql=null;
			//System.out.println("尺寸为："+orders.size());
			if(orders.size()>0)
			{
				String sqlOrder="";
				for(int i=0;i<orders.size();i++)
				{
					if(i!=orders.size()-1)
					{
						sqlOrder+=orders.get(i)+",";
					}
					else
						sqlOrder+=orders.get(i);
				}
				sql="select * from (select * from patent order by "+sqlOrder+") as p ,teacher as t where p.teaId=t.teaId and t.direction=? and p.examine>'"+state+"'";
			}
			else
			sql="select * from patent as P,teacher as T where P.teaId=T.teaId and T.direction=? and examine>'"+state+"' ";
			int j=0;
			String sql2=" and patId LIMIT "+(pageNow-1)+" ,"+pageSize+" ";
			List<Patent> patents=new ArrayList<Patent>();
			List<String> sql1=new ArrayList<String>();
			List<String> paras=new ArrayList<String>();
			
			paras.add(String.valueOf(direction));
			if(patent!=null)
			{
				if(patent.getName()!=null)
				{
					sql1.add(" P.name like ? ");
					paras.add("%"+String.valueOf(patent.getName())+"%");
				}
				if(patent.getFiestInventor()!=null)
				{
					sql1.add(" P.fiestInventor like ? ");
					paras.add("%"+String.valueOf(patent.getFiestInventor())+"%");
				}
				if(patent.getEmpowerDate()!=null)
				{
					sql1.add(" P.empowerDate>=? and P.empowerDate<=? ");
					paras.add(String.valueOf(patent.getEmpowerDate()));
					paras.add(empowerDate2);
				}
				if(patent.getEmpowerUnit()!=null)
				{
					sql1.add(" P.empowerUnit=? ");
					paras.add(String.valueOf(patent.getEmpowerUnit()));
				}
				if(patent.getApplyNumber()!=null)
				{
					sql1.add(" P.applyNumber=? ");
					paras.add(String.valueOf(patent.getNumber()));
				}
				if(patent.getExpClass()!=null)
				{
					sql1.add(" P.expClass=? ");
					paras.add(String.valueOf(patent.getExpClass()));
				}
				if(patent.getEmpowerNum()!=null)
				{
					sql1.add(" P.empowerNum=? ");
					paras.add(String.valueOf(patent.getEmpowerNum()));
				}
				if(patent.getApplyDate()!=null)
				{
					sql1.add(" P.applyDate>=? and P.applyDate<=? ");
					paras.add(String.valueOf(patent.getApplyDate()));
					paras.add(applyDate2);
				}
			}
			for(int i=0;i<sql1.size();i++)
			{
					sql+="and"+sql1.get(i);
			}
			
			//加入最后一个,该字符是用于分页
			sql+=sql2;
			System.out.println("语句是"+sql);
			String[] parameter=new String[paras.size()];
			for(int i=0;i<paras.size();i++)
			{
				parameter[i]=paras.get(i);
			}
			MysqlCrud mysqlcurd=new MysqlCrud();
			List<Map> list = mysqlcurd.SelectData(sql, parameter);
			while(j<list.size()){
				Map map=list.get(j);
				patents.add(new MapToBean().MapToPatent(map));
				j++;
			}
			return patents;
		}
		
		/**
		 * @author yaojun
		 * @category 根据不同条件查询所有专利,针对特定的方向
		 * @return patents List<Patent> 返回查询到的专利列表
		 */
		public List<Patent> getPatentByDirection(Patent patent,String empowerDate2,String applyDate2,int direction,int state) {
//			String sql= "select * from patent WHERE empowerDate>='"+date1+"' and empowerDate<='"+date2+"' and patId LIMIT " +
//					""+(pageNow-1)+" ,"+pageSize+"";
			String sql="select * from patent as P,teacher as T where P.teaId=T.teaId and T.direction=? and examine>'"+state+"' ";
			int j=0;
			List<Patent> patents=new ArrayList<Patent>();
			List<String> sql1=new ArrayList<String>();
			List<String> paras=new ArrayList<String>();
			
			paras.add(String.valueOf(direction));
			if(patent!=null)
			{
				if(patent.getName()!=null)
				{
					sql1.add(" P.name like ? ");
					paras.add("%"+String.valueOf(patent.getName())+"%");
				}
				if(patent.getFiestInventor()!=null)
				{
					sql1.add(" P.fiestInventor like ? ");
					paras.add("%"+String.valueOf(patent.getFiestInventor())+"%");
				}
				if(patent.getEmpowerDate()!=null)
				{
					sql1.add(" P.empowerDate>=? and P.empowerDate<=? ");
					paras.add(String.valueOf(patent.getEmpowerDate()));
					paras.add(empowerDate2);
				}
				if(patent.getEmpowerUnit()!=null)
				{
					sql1.add(" P.empowerUnit=? ");
					paras.add(String.valueOf(patent.getEmpowerUnit()));
				}
				if(patent.getApplyNumber()!=null)
				{
					sql1.add(" P.applyNumber=? ");
					paras.add(String.valueOf(patent.getNumber()));
				}
				if(patent.getExpClass()!=null)
				{
					sql1.add(" P.expClass=? ");
					paras.add(String.valueOf(patent.getExpClass()));
				}
				if(patent.getEmpowerNum()!=null)
				{
					sql1.add(" P.empowerNum=? ");
					paras.add(String.valueOf(patent.getEmpowerNum()));
				}
				if(patent.getApplyDate()!=null)
				{
					sql1.add(" P.applyDate>=? and P.applyDate<=? ");
					paras.add(String.valueOf(patent.getApplyDate()));
					paras.add(applyDate2);
				}
			}
			for(int i=0;i<sql1.size();i++)
			{
				sql+="and"+sql1.get(i);
			}
			
			String[] parameter=new String[paras.size()];
			for(int i=0;i<paras.size();i++)
			{
				parameter[i]=paras.get(i);
			}
			MysqlCrud mysqlcurd=new MysqlCrud();
			List<Map> list = mysqlcurd.SelectData(sql, parameter);
			while(j<list.size()){
				Map map=list.get(j);
				patents.add(new MapToBean().MapToPatent(map));
				j++;
			}
			return patents;
		}
		
		
		/**
		 * @author yaojun
		 * @category 根据不同条件查询所有专利
		 * @return patents List<Patent> 返回查询到的专利列表
		 */
		public List<Patent> getPatentByAll(Patent patent,String empowerDate2,String applyDate2,int direction,int state) {
//			String sql= "select * from patent WHERE empowerDate>='"+date1+"' and empowerDate<='"+date2+"' and patId LIMIT " +
//					""+(pageNow-1)+" ,"+pageSize+"";
			String sql="select * from patent as P,teacher as T where P.teaId=T.teaId and T.direction=? and examine>'"+state+"' ";
			int j=0;
			List<Patent> patents=new ArrayList<Patent>();
			List<String> sql1=new ArrayList<String>();
			List<String> paras=new ArrayList<String>();
			
			paras.add(String.valueOf(direction));
		
			if(patent!=null)
			{
				if(patent.getName()!=null)
				{
					sql1.add(" P.name like ? ");
					paras.add("%"+String.valueOf(patent.getName())+"%");
				}
				if(patent.getFiestInventor()!=null)
				{
					sql1.add(" P.fiestInventor like ? ");
					paras.add("%"+String.valueOf(patent.getFiestInventor())+"%");
				}
				if(patent.getEmpowerDate()!=null)
				{
					sql1.add(" P.empowerDate>=? and P.empowerDate<=? ");
					paras.add(String.valueOf(patent.getEmpowerDate()));
					paras.add(empowerDate2);
				}
				if(patent.getEmpowerUnit()!=null)
				{
					sql1.add(" P.empowerUnit=? ");
					paras.add(String.valueOf(patent.getEmpowerUnit()));
				}
				if(patent.getApplyNumber()!=null)
				{
					sql1.add(" P.applyNumber=? ");
					paras.add(String.valueOf(patent.getNumber()));
				}
				if(patent.getExpClass()!=null)
				{
					sql1.add(" P.expClass=? ");
					paras.add(String.valueOf(patent.getExpClass()));
				}
				if(patent.getEmpowerNum()!=null)
				{
					sql1.add(" P.empowerNum=? ");
					paras.add(String.valueOf(patent.getEmpowerNum()));
				}
				if(patent.getApplyDate()!=null)
				{
					sql1.add(" P.applyDate>=? and P.applyDate<=? ");
					paras.add(String.valueOf(patent.getApplyDate()));
					paras.add(applyDate2);
				}
			}
			for(int i=0;i<sql1.size();i++)
			{
				sql+="and"+sql1.get(i);
			}
			
			String[] parameter=new String[paras.size()];
			for(int i=0;i<paras.size();i++)
			{
				parameter[i]=paras.get(i);
			}
			MysqlCrud mysqlcurd=new MysqlCrud();
			List<Map> list = mysqlcurd.SelectData(sql, parameter);
			while(j<list.size()){
				Map map=list.get(j);
				patents.add(new MapToBean().MapToPatent(map));
				j++;
			}
			return patents;
		}
		
		
		/**
		 * @author yaojun
		 * @category 根据不同条件查询所有专利的数量，针对特定的方向
		 * @return int
		 */
		public int getPatentCountByDirection(Patent patent,String empowerDate2,String applyDate2,int direction,int state) {
//			String sql= "select * from patent WHERE empowerDate>='"+date1+"' and empowerDate<='"+date2+"' and patId LIMIT " +
//					""+(pageNow-1)+" ,"+pageSize+"";
			String sql="select count(*) from patent as P,teacher as T where P.teaId=T.teaId and T.direction=? and examine>'"+state+"' ";
			int count=0;
			List<String> sql1=new ArrayList<String>();
			List<String> paras=new ArrayList<String>();
			
			paras.add(String.valueOf(direction));
			if(patent!=null)
			{
				if(patent.getName()!=null)
				{
					sql1.add(" P.name like ? ");
					paras.add("%"+String.valueOf(patent.getName())+"%");
				}
				if(patent.getFiestInventor()!=null)
				{
					sql1.add(" P.fiestInventor like ? ");
					paras.add("%"+String.valueOf(patent.getFiestInventor())+"%");
				}
				if(patent.getEmpowerDate()!=null)
				{
					sql1.add(" P.empowerDate>=? and P.empowerDate<=? ");
					paras.add(String.valueOf(patent.getEmpowerDate()));
					paras.add(empowerDate2);
				}
				if(patent.getEmpowerUnit()!=null)
				{
					sql1.add(" P.empowerUnit=? ");
					paras.add(String.valueOf(patent.getEmpowerUnit()));
				}
				if(patent.getApplyNumber()!=null)
				{
					sql1.add(" P.applyNumber=? ");
					paras.add(String.valueOf(patent.getNumber()));
				}
				if(patent.getExpClass()!=null)
				{
					sql1.add(" P.expClass=? ");
					paras.add(String.valueOf(patent.getExpClass()));
				}
				if(patent.getEmpowerNum()!=null)
				{
					sql1.add(" P.empowerNum=? ");
					paras.add(String.valueOf(patent.getEmpowerNum()));
				}
				if(patent.getApplyDate()!=null)
				{
					sql1.add(" P.applyDate>=? and P.applyDate<=? ");
					paras.add(String.valueOf(patent.getApplyDate()));
					paras.add(applyDate2);
				}
			}
			for(int i=0;i<sql1.size();i++)
			{
				sql+="and"+sql1.get(i);
			}
			String[] parameter=new String[paras.size()];
			for(int i=0;i<paras.size();i++)
			{
				parameter[i]=paras.get(i);
			}
			MysqlCrud mysqlcurd=new MysqlCrud();
			List<Map> lists=mysqlcurd.SelectData(sql, parameter);
			Map map=lists.get(0);
			count=Integer.parseInt(map.get(0).toString());
			return count;
		}
		
		
		/**
		 * @author yaojun
		 * @category 根据不同条件查询所有专利,针对特定的教师工号
		 * @return List
		 */
		public List<Patent> getPatentByUser(Patent patent,String empowerDate2,String applyDate2,Teacher teacher) {
//			String sql= "select * from patent WHERE empowerDate>='"+date1+"' and empowerDate<='"+date2+"' and patId LIMIT " +
//					""+(pageNow-1)+" ,"+pageSize+"";
			String sql="select * from patent as P,teacher as T where P.teaId=T.teaId and T.user=? ";
			int j=0;
			List<Patent> patents=new ArrayList<Patent>();
			List<String> sql1=new ArrayList<String>();
			List<String> paras=new ArrayList<String>();
			
			paras.add(teacher.getUser());
			if(patent!=null)
			{
				if(patent.getName()!=null)
				{
					sql1.add(" P.name like ? ");
					paras.add("%"+String.valueOf(patent.getName())+"%");
				}
				if(patent.getFiestInventor()!=null)
				{
					sql1.add(" P.fiestInventor like ? ");
					paras.add("%"+String.valueOf(patent.getFiestInventor())+"%");
				}
				if(patent.getEmpowerDate()!=null)
				{
					sql1.add(" P.empowerDate>=? and P.empowerDate<=? ");
					paras.add(String.valueOf(patent.getEmpowerDate()));
					paras.add(empowerDate2);
				}
				if(patent.getEmpowerUnit()!=null)
				{
					sql1.add(" P.empowerUnit=? ");
					paras.add(String.valueOf(patent.getEmpowerUnit()));
				}
				if(patent.getApplyNumber()!=null)
				{
					sql1.add(" P.applyNumber=? ");
					paras.add(String.valueOf(patent.getNumber()));
				}
				if(patent.getExpClass()!=null)
				{
					sql1.add(" P.expClass=? ");
					paras.add(String.valueOf(patent.getExpClass()));
				}
				if(patent.getEmpowerNum()!=null)
				{
					sql1.add(" P.empowerNum=? ");
					paras.add(String.valueOf(patent.getEmpowerNum()));
				}
				if(patent.getApplyDate()!=null)
				{
					sql1.add(" P.applyDate>=? and P.applyDate<=? ");
					paras.add(String.valueOf(patent.getApplyDate()));
					paras.add(applyDate2);
				}
			}
			for(int i=0;i<sql1.size();i++)
			{
				sql+="and"+sql1.get(i);
			}
			String[] parameter=new String[paras.size()];
			for(int i=0;i<paras.size();i++)
			{
				parameter[i]=paras.get(i);
			}
			MysqlCrud mysqlcurd=new MysqlCrud();
			List<Map> list = mysqlcurd.SelectData(sql, parameter);
			while(j<list.size()){
				Map map=list.get(j);
				patents.add(new MapToBean().MapToPatent(map));
				j++;
			}
			return patents;
		}
}
